package com.example.demo.generator.codegen;

import com.example.demo.generator.codegen.exception.CodeGenerateException;
import com.example.demo.generator.codegen.exception.SqlParseException;
import com.example.demo.generator.codegen.model.ClassInfo;
import com.example.demo.generator.codegen.model.FieldInfo;
import com.example.demo.generator.codegen.model.IndexInfo;
import com.example.demo.generator.codegen.model.ParamInfo;
import com.example.demo.generator.codegen.util.MapUtils;
import com.example.demo.generator.codegen.util.NonCaseString;
import com.example.demo.generator.codegen.util.StringCaseUtils;
import com.example.demo.generator.config.CodeGeneratorProperties;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.io.StringReader;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Lightweight adapter around the open-source SpringBootCodeGenerator SQL parsing utilities.
 * <p>
 * The goal is to expose SpringBootCodeGenerator's powerful SQL ��� metadata conversion without
 * pulling its entire web stack into the template project.
 */
@Slf4j
@Component
public class SpringBootCodeGeneratorAdapter {

    private final CCJSqlParserManager parserManager = new CCJSqlParserManager();
    private final CodeGeneratorProperties config;

    public SpringBootCodeGeneratorAdapter(CodeGeneratorProperties config) {
        this.config = config;
    }

    /**
     * Parse a CREATE TABLE statement and return the resulting metadata.
     */
    public ClassInfo parseCreateTable(ParamInfo paramInfo) throws SqlParseException {
        String sql = requireSql(paramInfo);
        List<IndexInfo> extractedTableIndexes = extractTableIndexes(sql);
        String processedSql = preprocess(sql);

        Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(processedSql);
        } catch (JSQLParserException ex) {
            int errorPosition = extractErrorPosition(ex.getMessage());
            String suggestion = generateParseSuggestion(sql, errorPosition);
            throw new SqlParseException(
                "CREATE TABLE 语句解析失败: " + ex.getMessage(),
                sql,
                errorPosition,
                suggestion,
                "CREATE_TABLE_PARSE_ERROR",
                ex
            );
        }

        if (!(statement instanceof CreateTable)) {
            String suggestion = "请确保输入的是有效的 CREATE TABLE 语句，例如：CREATE TABLE user (id BIGINT PRIMARY KEY, name VARCHAR(50));";
            throw new SqlParseException(
                "检测到的 SQL 不是 CREATE TABLE 语句",
                sql,
                -1,
                suggestion,
                "NOT_CREATE_TABLE_ERROR"
            );
        }

        CreateTable createTable = (CreateTable) statement;

        Map<String, Object> options = paramInfo.getOptions();
        String originTableName = cleanIdentifier(createTable.getTable().getFullyQualifiedName());
        String tableName = applyIgnorePrefix(originTableName, options);
        ClassInfo classInfo = new ClassInfo();
        classInfo.setOriginTableName(originTableName);
        classInfo.setTableName(tableName);
        classInfo.setClassName(deriveClassName(tableName));
        classInfo.setClassComment(sql);

        List<ColumnDefinition> definitions = createTable.getColumnDefinitions();
        List<FieldInfo> fieldInfos = new ArrayList<>();
        Map<String, FieldInfo> fieldByColumn = new java.util.LinkedHashMap<>();
        if (!CollectionUtils.isEmpty(definitions)) {
            for (ColumnDefinition definition : definitions) {
                FieldInfo fieldInfo = new FieldInfo();
                String columnName = cleanIdentifier(definition.getColumnName());
                fieldInfo.setColumnName(columnName);
                fieldInfo.setFieldName(convertFieldName(columnName, options));
                fieldInfo.setFieldClass(mapToJavaType(definition.getColDataType().getDataType()));
                fieldInfo.setSwaggerClass(fieldInfo.getFieldClass());
                fieldInfo.setFieldComment(extractComment(definition));
                applyColumnSpecs(definition, fieldInfo);
                fieldInfos.add(fieldInfo);
                fieldByColumn.put(columnName.toLowerCase(Locale.ROOT), fieldInfo);
            }
        }
        classInfo.setFieldList(fieldInfos);

        Map<String, IndexInfo> indexMap = new LinkedHashMap<>();
        extractedTableIndexes.forEach(indexInfo -> indexMap.put(buildIndexLookupKey(indexInfo), indexInfo));

        if (!CollectionUtils.isEmpty(createTable.getIndexes())) {
            for (Index index : createTable.getIndexes()) {
                IndexInfo info = new IndexInfo();
                info.setName(cleanIdentifier(index.getName()));
                info.setType(resolveIndexType(index.getType()));
                List<String> columns = index.getColumnsNames() != null
                        ? index.getColumnsNames().stream()
                        .map(this::cleanIdentifier)
                        .collect(Collectors.toList())
                        : List.of();
                info.setColumnNames(columns);
                if (info.getType() == IndexInfo.IndexType.PRIMARY && (info.getName() == null || info.getName().isBlank())) {
                    info.setName("PRIMARY");
                }
                String key = buildIndexLookupKey(info);
                indexMap.putIfAbsent(key, info);
            }
        }

        List<IndexInfo> indexInfos = new ArrayList<>(indexMap.values());
        LinkedHashSet<String> primaryColumnSet = new LinkedHashSet<>();

        for (IndexInfo indexInfo : indexInfos) {
            if (indexInfo.getType() == IndexInfo.IndexType.PRIMARY) {
                if (indexInfo.getName() == null || indexInfo.getName().isBlank()) {
                    indexInfo.setName("PRIMARY");
                }
                for (String column : indexInfo.getColumnNames()) {
                    primaryColumnSet.add(column);
                    FieldInfo field = fieldByColumn.get(column.toLowerCase(Locale.ROOT));
                    if (field != null) {
                        field.setPrimaryKey(true);
                        field.setNullable(false);
                    }
                }
            }

            if (indexInfo.isUnique() && indexInfo.getColumnNames().size() == 1) {
                String column = indexInfo.getColumnNames().get(0);
                FieldInfo field = fieldByColumn.get(column.toLowerCase(Locale.ROOT));
                if (field != null) {
                    field.setUnique(true);
                }
            }
        }

        classInfo.setPrimaryKeyColumns(new ArrayList<>(primaryColumnSet));
        classInfo.setIndexList(indexInfos);

        log.debug("Parsed CREATE TABLE metadata: {}", classInfo);
        return classInfo;
    }

    /**
     * Parse a SELECT statement and derive a pseudo class definition (useful for view generation).
     */
    public ClassInfo parseSelectSql(ParamInfo paramInfo) throws SqlParseException {
        String sql = requireSql(paramInfo);
        String processedSql = preprocess(sql);

        Statement statement;
        try (StringReader reader = new StringReader(processedSql)) {
            statement = parserManager.parse(reader);
        } catch (JSQLParserException ex) {
            throw new SqlParseException("SELECT 语句解析失败: " + ex.getMessage(), ex);
        }

        List<String> tableNames = new TablesNamesFinder().getTableList(statement);
        String rawTableName = tableNames.isEmpty() ? "anonymous_select" : tableNames.get(0);
        String tableName = applyIgnorePrefix(cleanIdentifier(rawTableName), paramInfo.getOptions());

        ClassInfo classInfo = new ClassInfo();
        classInfo.setOriginTableName(cleanIdentifier(rawTableName));
        classInfo.setTableName(tableName);
        classInfo.setClassName(deriveClassName(tableName));
        classInfo.setClassComment(sql);

        Select select;
        try {
            select = (Select) CCJSqlParserUtil.parse(processedSql);
        } catch (JSQLParserException ex) {
            throw new SqlParseException("SELECT 语句解析失败: " + ex.getMessage(), ex);
        }

        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        List<FieldInfo> fields = new ArrayList<>();
        for (SelectItem selectItem : plainSelect.getSelectItems()) {
            Column column = extractColumn(selectItem);
            if (column == null) {
                continue;
            }
            FieldInfo fieldInfo = new FieldInfo();
            String columnName = cleanIdentifier(column.getColumnName());
            String alias = extractAlias(selectItem, columnName);
            fieldInfo.setColumnName(columnName);
            fieldInfo.setFieldName(convertFieldName(alias, paramInfo.getOptions()));
            fieldInfo.setFieldClass("String");
            fieldInfo.setSwaggerClass("String");
            fieldInfo.setFieldComment(alias);
            fields.add(fieldInfo);
        }

        classInfo.setFieldList(fields);
        log.debug("Parsed SELECT metadata: {}", classInfo);
        return classInfo;
    }

    /**
     * Rough equivalent of SpringBootCodeGenerator's legacy regex parser as a fallback for
     * edge cases JSqlParser cannot handle cleanly yet.
     */
    public ClassInfo fallbackProcessTable(ParamInfo paramInfo) {
        String sql = requireSql(paramInfo);
        NonCaseString tableSql = NonCaseString.of(sql);
        NonCaseString trimmed = tableSql.trim()
                .replaceAll("\"", "`")
                .replaceAll("，", ",");
        if (!trimmed.contains("TABLE") || !trimmed.contains("(")) {
            throw new CodeGenerateException("Table structure incorrect. 表结构不正确。");
        }
        String rawTableName = trimmed.substring(trimmed.indexOf("TABLE") + 5, trimmed.indexOf("(")).get();
        if (rawTableName.contains("if not exists")) {
            rawTableName = rawTableName.replaceAll("if not exists", "");
        }
        rawTableName = cleanIdentifier(rawTableName);
        String tableName = applyIgnorePrefix(rawTableName, paramInfo.getOptions());

        ClassInfo classInfo = new ClassInfo();
        classInfo.setOriginTableName(rawTableName);
        classInfo.setTableName(tableName);
        classInfo.setClassName(deriveClassName(tableName));
        classInfo.setClassComment(sql);
        classInfo.setFieldList(new ArrayList<>());
        return classInfo;
    }

    private String requireSql(ParamInfo paramInfo) {
        if (paramInfo == null || StringUtils.isBlank(paramInfo.getTableSql())) {
            throw new CodeGenerateException("SQL 内容不能为空");
        }
        return paramInfo.getTableSql();
    }

    private String preprocess(String sql) {
        String normalized = removeSqlComments(sql);

        normalized = normalized.trim()
                .replaceAll("\"", "`")
                .replaceAll("，", ",");

        // MySQL 允许在索引定义末尾写 COMMENT，JSqlParser 暂不支持；提前剔除该段落以免解析报错
        normalized = normalized.replaceAll(
                "(?is)(UNIQUE\\s+KEY\\s+[^,\\r\\n]+?)\\s+COMMENT\\s+'[^']*'",
                "$1"
        );
        normalized = normalized.replaceAll(
                "(?is)(KEY\\s+[^,\\r\\n]+?)\\s+COMMENT\\s+'[^']*'",
                "$1"
        );

        return stripTableLevelConstraints(normalized);
    }

    private String removeSqlComments(String sql) {
        if (sql == null) {
            return "";
        }
        return sql
                .replaceAll("(?m)^\\s*--.*$", "")
                .replaceAll("(?m)^\\s*#.*$", "")
                .replaceAll("(?s)/\\*.*?\\*/", "");
    }

    private String stripTableLevelConstraints(String sql) {
        int start = sql.indexOf('(');
        int end = sql.lastIndexOf(')');
        if (start < 0 || end <= start) {
            return sql;
        }

        String header = sql.substring(0, start + 1);
        String body = sql.substring(start + 1, end);
        String tail = sql.substring(end);

        List<String> segments = splitTopLevelSegments(body);
        List<String> kept = new ArrayList<>();
        for (String segment : segments) {
            String trimmed = segment.trim();
            if (trimmed.isEmpty()) {
                continue;
            }
            String upper = trimmed.toUpperCase(Locale.ROOT);
            if (upper.contains("PRIMARY KEY")) {
                kept.add(trimmed);
                continue;
            }
            if (upper.startsWith("CONSTRAINT") && (upper.contains(" UNIQUE ") || upper.contains(" KEY ") || upper.contains("FOREIGN KEY"))) {
                continue;
            }
            if (upper.startsWith("UNIQUE ") || upper.startsWith("FULLTEXT ") || upper.startsWith("SPATIAL ")) {
                continue;
            }
            if (upper.startsWith("KEY ") || upper.startsWith("UNIQUE KEY") || upper.startsWith("FOREIGN KEY")) {
                continue;
            }
            kept.add(trimmed);
        }

        String joined = String.join(",\n  ", kept);
        return header + "\n  " + joined + "\n" + tail;
    }

    private List<String> splitTopLevelSegments(String body) {
        List<String> segments = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        boolean inSingleQuote = false;
        boolean inBacktick = false;
        int parenDepth = 0;

        for (int i = 0; i < body.length(); i++) {
            char ch = body.charAt(i);
            if (ch == '\'' && !inBacktick) {
                boolean escaped = i > 0 && body.charAt(i - 1) == '\\';
                if (!escaped) {
                    inSingleQuote = !inSingleQuote;
                }
            } else if (ch == '`' && !inSingleQuote) {
                inBacktick = !inBacktick;
            } else if (!inSingleQuote && !inBacktick) {
                if (ch == '(') {
                    parenDepth++;
                } else if (ch == ')') {
                    if (parenDepth > 0) {
                        parenDepth--;
                    }
                } else if (ch == ',' && parenDepth == 0) {
                    segments.add(current.toString());
                    current.setLength(0);
                    continue;
                }
            }
            current.append(ch);
        }

        if (current.length() > 0) {
            segments.add(current.toString());
        }
        return segments;
    }

    private List<IndexInfo> extractTableIndexes(String sql) {
        if (StringUtils.isBlank(sql)) {
            return List.of();
        }
        String normalized = removeSqlComments(sql).trim()
                .replaceAll("\"", "`")
                .replaceAll("，", ",");
        int start = normalized.indexOf('(');
        int end = normalized.lastIndexOf(')');
        if (start < 0 || end <= start) {
            return List.of();
        }

        String body = normalized.substring(start + 1, end);
        List<String> segments = splitTopLevelSegments(body);
        if (segments.isEmpty()) {
            return List.of();
        }

        List<IndexInfo> result = new ArrayList<>();
        for (String segment : segments) {
            IndexInfo indexInfo = parseIndexDefinition(segment.trim());
            if (indexInfo != null && !indexInfo.getColumnNames().isEmpty()) {
                result.add(indexInfo);
            }
        }
        return result;
    }

    private IndexInfo parseIndexDefinition(String rawSegment) {
        if (rawSegment == null) {
            return null;
        }
        String segment = rawSegment.trim();
        if (segment.isEmpty()) {
            return null;
        }

        String upper = segment.toUpperCase(Locale.ROOT);
        if (!(upper.startsWith("PRIMARY")
                || upper.startsWith("UNIQUE")
                || upper.startsWith("KEY")
                || upper.startsWith("INDEX")
                || upper.startsWith("CONSTRAINT")
                || upper.startsWith("FULLTEXT")
                || upper.startsWith("SPATIAL")
                || upper.startsWith("FOREIGN"))) {
            return null;
        }

        segment = segment.replaceAll("(?i)WITH\\s+PARSER\\s+[^\\s,]+", "");
        segment = segment.replaceAll("(?i)USING\\s+BTREE", "USING BTREE");
        segment = segment.replaceAll("(?i)USING\\s+HASH", "USING HASH");
        segment = segment.replaceAll("(?i)COMMENT\\s+'[^']*'", "");

        if (upper.startsWith("CONSTRAINT")) {
            segment = stripConstraintPrefix(segment);
            if (segment.isEmpty()) {
                return null;
            }
            upper = segment.toUpperCase(Locale.ROOT);
        }

        if (upper.startsWith("PRIMARY")) {
            return buildIndexInfoForSegment(segment, IndexInfo.IndexType.PRIMARY, "PRIMARY");
        }
        if (upper.startsWith("UNIQUE")) {
            return buildIndexInfoForSegment(segment, IndexInfo.IndexType.UNIQUE, "UNIQUE");
        }
        if (upper.startsWith("FULLTEXT")) {
            return buildIndexInfoForSegment(segment, IndexInfo.IndexType.FULLTEXT, "FULLTEXT");
        }
        if (upper.startsWith("SPATIAL")) {
            return buildIndexInfoForSegment(segment, IndexInfo.IndexType.SPATIAL, "SPATIAL");
        }
        if (upper.startsWith("FOREIGN")) {
            return buildIndexInfoForSegment(segment, IndexInfo.IndexType.UNKNOWN, "FOREIGN");
        }
        return buildIndexInfoForSegment(segment, IndexInfo.IndexType.INDEX, null);
    }

    private IndexInfo buildIndexInfoForSegment(String segment, IndexInfo.IndexType type, String leadingKeyword) {
        String working = segment.trim();
        if (leadingKeyword != null && !leadingKeyword.isBlank()) {
            working = consumeLeadingKeyword(working, leadingKeyword);
        }

        if (type == IndexInfo.IndexType.PRIMARY) {
            working = consumeLeadingKeyword(working, "KEY");
        } else if (type == IndexInfo.IndexType.UNIQUE
                || type == IndexInfo.IndexType.FULLTEXT
                || type == IndexInfo.IndexType.SPATIAL
                || type == IndexInfo.IndexType.UNKNOWN) {
            working = consumeLeadingKeyword(working, "KEY");
            working = consumeLeadingKeyword(working, "INDEX");
        } else {
            working = consumeLeadingKeyword(working, "INDEX");
            working = consumeLeadingKeyword(working, "KEY");
        }

        List<String> prefixTokens = extractTokensBeforeColumns(working);
        String indexName = extractIndexName(prefixTokens);
        String columnSection = extractColumnSection(working);
        if (columnSection == null) {
            return null;
        }
        List<String> columnNames = parseColumnNames(columnSection);
        if (columnNames.isEmpty()) {
            return null;
        }

        IndexInfo indexInfo = new IndexInfo();
        indexInfo.setType(type != null ? type : IndexInfo.IndexType.UNKNOWN);
        indexInfo.setName(indexName);
        indexInfo.setColumnNames(columnNames);
        return indexInfo;
    }

    private List<String> extractTokensBeforeColumns(String text) {
        List<String> tokens = new ArrayList<>();
        String working = text == null ? "" : text.trim();
        while (!working.isEmpty() && working.charAt(0) != '(') {
            IdentifierToken token = consumeIdentifierToken(working);
            if (token.identifier == null) {
                working = token.remainder != null ? token.remainder : "";
                break;
            }
            tokens.add(token.identifier);
            working = token.remainder != null ? token.remainder.trim() : "";
        }
        return tokens;
    }

    private String extractIndexName(List<String> tokens) {
        if (tokens == null || tokens.isEmpty()) {
            return null;
        }
        for (int i = tokens.size() - 1; i >= 0; i--) {
            String token = tokens.get(i);
            String cleaned = cleanIdentifier(token);
            if (cleaned == null || cleaned.isBlank()) {
                continue;
            }
            if (isIndexOptionToken(cleaned)) {
                continue;
            }
            return cleaned;
        }
        return null;
    }

    private boolean isIndexOptionToken(String token) {
        if (token == null || token.isBlank()) {
            return true;
        }
        String normalized = token.trim().replace("`", "");
        if (normalized.isEmpty()) {
            return true;
        }
        String upper = normalized.toUpperCase(Locale.ROOT);
        if (upper.equals("USING")
                || upper.equals("BTREE")
                || upper.equals("HASH")
                || upper.equals("VISIBLE")
                || upper.equals("INVISIBLE")
                || upper.equals("WITH")
                || upper.equals("PARSER")
                || upper.equals("COMMENT")
                || upper.equals("KEY_BLOCK_SIZE")) {
            return true;
        }
        if (upper.equals("=") || upper.matches("[0-9]+")) {
            return true;
        }
        return false;
    }

    private IdentifierToken consumeIdentifierToken(String text) {
        if (text == null) {
            return new IdentifierToken(null, "");
        }
        String trimmed = text.trim();
        if (trimmed.isEmpty()) {
            return new IdentifierToken(null, "");
        }
        char first = trimmed.charAt(0);
        if (first == '(') {
            return new IdentifierToken(null, trimmed);
        }
        if (first == '`' || first == '\'' || first == '"') {
            int end = findClosingQuote(trimmed, first);
            if (end > 0) {
                String identifier = trimmed.substring(0, end + 1);
                String remainder = trimmed.substring(end + 1);
                return new IdentifierToken(identifier, remainder.trim());
            }
        }
        int idx = 0;
        while (idx < trimmed.length() && !Character.isWhitespace(trimmed.charAt(idx)) && trimmed.charAt(idx) != '(') {
            idx++;
        }
        if (idx == 0) {
            return new IdentifierToken(null, trimmed);
        }
        String identifier = trimmed.substring(0, idx);
        String remainder = trimmed.substring(idx);
        return new IdentifierToken(identifier, remainder.trim());
    }

    private int findClosingQuote(String text, char quote) {
        for (int i = 1; i < text.length(); i++) {
            if (text.charAt(i) == quote) {
                return i;
            }
        }
        return -1;
    }

    private String extractColumnSection(String text) {
        if (text == null) {
            return null;
        }
        String working = text.trim();
        boolean inSingleQuote = false;
        boolean inBacktick = false;
        int depth = 0;
        int start = -1;
        for (int i = 0; i < working.length(); i++) {
            char ch = working.charAt(i);
            if (ch == '\'' && !inBacktick) {
                boolean escaped = i > 0 && working.charAt(i - 1) == '\\';
                if (!escaped) {
                    inSingleQuote = !inSingleQuote;
                }
            } else if (ch == '`' && !inSingleQuote) {
                inBacktick = !inBacktick;
            } else if (!inSingleQuote && !inBacktick) {
                if (ch == '(') {
                    if (depth == 0) {
                        start = i + 1;
                    }
                    depth++;
                } else if (ch == ')') {
                    depth--;
                    if (depth == 0 && start >= 0) {
                        return working.substring(start, i);
                    }
                }
            }
        }
        return null;
    }

    private List<String> parseColumnNames(String columnsSegment) {
        if (columnsSegment == null) {
            return List.of();
        }
        List<String> expressions = splitTopLevelSegments(columnsSegment);
        if (expressions.isEmpty()) {
            expressions = List.of(columnsSegment);
        }
        List<String> columns = new ArrayList<>();
        for (String expression : expressions) {
            String identifier = extractColumnIdentifier(expression);
            if (identifier == null || identifier.isBlank()) {
                continue;
            }
            String cleaned = cleanIdentifier(identifier);
            if (cleaned != null && !cleaned.isBlank()) {
                columns.add(cleaned);
            }
        }
        return columns;
    }

    private String extractColumnIdentifier(String columnExpr) {
        if (columnExpr == null) {
            return null;
        }
        String trimmed = columnExpr.trim();
        if (trimmed.isEmpty()) {
            return null;
        }
        char first = trimmed.charAt(0);
        if (first == '`' || first == '\'' || first == '"') {
            int end = findClosingQuote(trimmed, first);
            if (end > 0) {
                return trimmed.substring(0, end + 1);
            }
        }
        int idx = 0;
        while (idx < trimmed.length() && !Character.isWhitespace(trimmed.charAt(idx)) && trimmed.charAt(idx) != '(' && trimmed.charAt(idx) != ',') {
            idx++;
        }
        if (idx == 0) {
            return null;
        }
        return trimmed.substring(0, idx);
    }

    private String buildIndexLookupKey(IndexInfo indexInfo) {
        if (indexInfo == null) {
            return "";
        }
        String name = indexInfo.getName() != null ? indexInfo.getName().toLowerCase(Locale.ROOT) : "";
        String type = indexInfo.getType() != null ? indexInfo.getType().name() : IndexInfo.IndexType.UNKNOWN.name();
        String columns = indexInfo.getColumnNames() == null ? "" : indexInfo.getColumnNames().stream()
                .map(value -> value == null ? "" : value.toLowerCase(Locale.ROOT))
                .collect(Collectors.joining(","));
        return type + "|" + columns + "|" + name;
    }

    private String stripConstraintPrefix(String segment) {
        String working = segment.trim();
        if (!working.toUpperCase(Locale.ROOT).startsWith("CONSTRAINT")) {
            return working;
        }
        working = working.substring("CONSTRAINT".length()).trim();
        IdentifierToken identifierToken = consumeIdentifierToken(working);
        if (identifierToken.identifier != null) {
            working = identifierToken.remainder != null ? identifierToken.remainder : "";
        } else {
            working = identifierToken.remainder != null ? identifierToken.remainder : working;
        }
        return working != null ? working.trim() : "";
    }

    private String consumeLeadingKeyword(String text, String keyword) {
        if (text == null) {
            return "";
        }
        String trimmed = text.trim();
        if (keyword == null || keyword.isBlank()) {
            return trimmed;
        }
        if (trimmed.length() < keyword.length()) {
            return trimmed;
        }
        if (trimmed.regionMatches(true, 0, keyword, 0, keyword.length())) {
            return trimmed.substring(keyword.length()).trim();
        }
        return trimmed;
    }

    private static final class IdentifierToken {
        private final String identifier;
        private final String remainder;

        private IdentifierToken(String identifier, String remainder) {
            this.identifier = identifier;
            this.remainder = remainder;
        }
    }

    private void applyColumnSpecs(ColumnDefinition definition, FieldInfo fieldInfo) {
        List<String> specs = resolveColumnSpecs(definition);
        if (CollectionUtils.isEmpty(specs)) {
            return;
        }
        String joinedLower = String.join(" ", specs).toLowerCase(Locale.ROOT);
        if (joinedLower.contains("primary key")) {
            fieldInfo.setPrimaryKey(true);
            fieldInfo.setNullable(false);
        }
        if (joinedLower.contains("unique")) {
            fieldInfo.setUnique(true);
        }
        if (joinedLower.contains("not null")) {
            fieldInfo.setNullable(false);
        }
        if (joinedLower.contains("auto_increment") || joinedLower.contains("identity")) {
            fieldInfo.setAutoIncrement(true);
        }
        for (int i = 0; i < specs.size(); i++) {
            String token = specs.get(i);
            if ("default".equalsIgnoreCase(token) && i + 1 < specs.size()) {
                String rawDefault = specs.get(i + 1);
                fieldInfo.setDefaultValue(cleanQuotes(rawDefault));
                break;
            }
        }
    }

    private IndexInfo.IndexType resolveIndexType(String type) {
        if (type == null || type.isBlank()) {
            return IndexInfo.IndexType.INDEX;
        }
        String normalized = type.trim().toUpperCase(Locale.ROOT);
        if (normalized.contains("PRIMARY")) {
            return IndexInfo.IndexType.PRIMARY;
        }
        if (normalized.contains("UNIQUE")) {
            return IndexInfo.IndexType.UNIQUE;
        }
        if (normalized.contains("FULLTEXT")) {
            return IndexInfo.IndexType.FULLTEXT;
        }
        if (normalized.contains("SPATIAL")) {
            return IndexInfo.IndexType.SPATIAL;
        }
        if (normalized.contains("INDEX") || normalized.contains("KEY")) {
            return IndexInfo.IndexType.INDEX;
        }
        return IndexInfo.IndexType.UNKNOWN;
    }

    private String cleanIdentifier(String identifier) {
        if (identifier == null) {
            return null;
        }
        String result = identifier.trim();
        if (result.contains("`")) {
            result = result.replace("`", "");
        }
        if (result.contains(".")) {
            result = result.substring(result.lastIndexOf('.') + 1);
        }
        return result;
    }

    private String deriveClassName(String tableName) {
        String base = StringCaseUtils.underlineToCamelCase(tableName);
        if (base.contains("_")) {
            base = base.replace("_", "");
        }
        return StringCaseUtils.upperCaseFirst(base);
    }

    private String convertFieldName(String columnName, Map<String, Object> options) {
        String caseType = MapUtils.getString(options, "nameCaseType");
        if (ParamInfo.NameCaseType.CAMEL_CASE.equals(caseType)) {
            return StringCaseUtils.toLowerCamel(columnName);
        }
        if (ParamInfo.NameCaseType.UNDER_SCORE_CASE.equals(caseType)) {
            return StringCaseUtils.toUnderline(columnName, false);
        }
        if (ParamInfo.NameCaseType.UPPER_UNDER_SCORE_CASE.equals(caseType)) {
            return StringCaseUtils.toUnderline(columnName.toUpperCase(Locale.ROOT), true);
        }
        return columnName;
    }

    private String applyIgnorePrefix(String tableName, Map<String, Object> options) {
        String ignorePrefix = MapUtils.getString(options, "ignorePrefix");
        if (StringCaseUtils.isNotNull(ignorePrefix) && StringUtils.startsWith(tableName, ignorePrefix)) {
            return tableName.substring(ignorePrefix.length());
        }
        return tableName;
    }

    private String extractComment(ColumnDefinition definition) {
        List<String> specs = resolveColumnSpecs(definition);
        if (CollectionUtils.isEmpty(specs)) {
            return "";
        }
        for (int i = 0; i < specs.size(); i++) {
            String value = specs.get(i);
            if ("comment".equalsIgnoreCase(value) && i + 1 < specs.size()) {
                return cleanQuotes(specs.get(i + 1));
            }
        }
        return "";
    }

    private String cleanQuotes(String text) {
        if (text == null) {
            return "";
        }
        String cleaned = text.trim();
        if (cleaned.length() >= 2 && cleaned.startsWith("'") && cleaned.endsWith("'")) {
            cleaned = cleaned.substring(1, cleaned.length() - 1);
        }
        if (cleaned.length() >= 2 && cleaned.startsWith("\"") && cleaned.endsWith("\"")) {
            cleaned = cleaned.substring(1, cleaned.length() - 1);
        }
        if (cleaned.length() >= 2 && cleaned.startsWith("`") && cleaned.endsWith("`")) {
            cleaned = cleaned.substring(1, cleaned.length() - 1);
        }
        return cleaned;
    }

    @SuppressWarnings("unchecked")
    private List<String> resolveColumnSpecs(ColumnDefinition definition) {
        try {
            // 兼容不同版本 JSqlParser：优先调用 getColumnSpecs()
            Method preferMethod = definition.getClass().getMethod("getColumnSpecs");
            Object value = preferMethod.invoke(definition);
            if (value instanceof List) {
                return ((List<Object>) value).stream()
                        .map(obj -> obj == null ? "" : obj.toString())
                        .collect(Collectors.toList());
            }
        } catch (NoSuchMethodException ignored) {
            // fall back to legacy method below
        } catch (ReflectiveOperationException ex) {
            log.debug("ColumnDefinition#getColumnSpecs unavailable: {}", ex.getMessage());
        }

        try {
            Method method = definition.getClass().getMethod("getColumnSpecStrings");
            Object value = method.invoke(definition);
            if (value instanceof List) {
                return ((List<Object>) value).stream()
                        .map(obj -> obj == null ? "" : obj.toString())
                        .collect(Collectors.toList());
            }
        } catch (ReflectiveOperationException ex) {
            log.debug("ColumnDefinition#getColumnSpecStrings unavailable: {}", ex.getMessage());
            return Collections.emptyList();
        }
        return Collections.emptyList();
    }

    private String mapToJavaType(String dataType) {
        if (dataType == null || dataType.isBlank()) {
            return defaultJavaType();
        }

        ParsedSqlType parsed = ParsedSqlType.parse(dataType);
        Map<String, String> mappings = config.getTypeMapping().getMappings();

        // 允许在配置中针对具体精度声明映射（如 decimal(10,0)）。
        String exactWithArgs = findMappingForKey(mappings, parsed.getBaseWithArguments());
        if (exactWithArgs != null) {
            return exactWithArgs;
        }

        String direct = findMappingForKey(mappings, parsed.getBaseType());

        String heuristic = resolveHeuristicType(parsed, direct);
        if (heuristic != null) {
            return heuristic;
        }

        if (direct != null) {
            return direct;
        }

        String fuzzy = findFuzzyMapping(mappings, parsed.getNormalized());
        if (fuzzy != null) {
            return fuzzy;
        }

        return defaultJavaType();
    }

    private String resolveHeuristicType(ParsedSqlType parsed, String directMapping) {
        String baseType = parsed.getBaseType();
        Integer precision = parsed.getPrimaryLength();
        Integer scale = parsed.getScale();

        if ("tinyint".equals(baseType)) {
            if (precision != null && precision == 1) {
                return findMappingForKey(config.getTypeMapping().getMappings(), "boolean");
            }
            if (directMapping != null) {
                return directMapping;
            }
            return "Integer";
        }

        if ("bit".equals(baseType)) {
            if (precision != null && precision <= 1) {
                return findMappingForKey(config.getTypeMapping().getMappings(), "boolean");
            }
        }

        if ("bool".equals(baseType) || "boolean".equals(baseType)) {
            return "Boolean";
        }

        if ("json".equals(baseType) || "enum".equals(baseType) || "set".equals(baseType)) {
            return "String";
        }

        if (("decimal".equals(baseType) || "numeric".equals(baseType)) && scale != null) {
            int numericScale = scale;
            int numericPrecision = precision != null ? precision : 0;
            if (numericScale == 0 && numericPrecision > 0) {
                if (numericPrecision <= 9) {
                    return "Integer";
                }
                if (numericPrecision <= 18) {
                    return "Long";
                }
            }
            if (directMapping != null) {
                return directMapping;
            }
            return "java.math.BigDecimal";
        }

        return directMapping;
    }

    private String findMappingForKey(Map<String, String> mappings, String key) {
        if (key == null || mappings == null) {
            return null;
        }
        String direct = mappings.get(key);
        if (direct != null) {
            return direct;
        }
        for (Map.Entry<String, String> entry : mappings.entrySet()) {
            if (entry.getKey() != null && entry.getKey().equalsIgnoreCase(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    private String findFuzzyMapping(Map<String, String> mappings, String normalized) {
        if (mappings == null || normalized == null) {
            return null;
        }
        for (Map.Entry<String, String> entry : mappings.entrySet()) {
            if (entry.getKey() == null) {
                continue;
            }
            String loweredKey = entry.getKey().toLowerCase(Locale.ROOT);
            if (normalized.contains(loweredKey)) {
                return entry.getValue();
            }
        }
        return null;
    }

    private String defaultJavaType() {
        String configured = config.getTypeMapping().getDefaultType();
        if (configured != null && !configured.isBlank()) {
            return configured;
        }
        return "String";
    }

    private static final class ParsedSqlType {
        private final String original;
        private final String normalized;
        private final String baseType;
        private final String baseWithArguments;
        private final String lengthPart;

        private ParsedSqlType(String original,
                              String normalized,
                              String baseType,
                              String baseWithArguments,
                              String lengthPart) {
            this.original = original;
            this.normalized = normalized;
            this.baseType = baseType;
            this.baseWithArguments = baseWithArguments;
            this.lengthPart = lengthPart;
        }

        static ParsedSqlType parse(String raw) {
            if (raw == null) {
                return new ParsedSqlType(null, "", "", "", null);
            }

            String normalized = raw.trim().toLowerCase(Locale.ROOT);
            normalized = normalized.replaceAll("\\s+", " ");

            String sanitized = normalized
                    .replace(" (", "(")
                    .replaceAll("\\s+unsigned", "")
                    .replaceAll("\\s+zerofill", "")
                    .replaceAll("\\s+binary", "")
                    .replaceAll("\\s+character\\s+set\\s+[a-z0-9_-]+", "")
                    .replaceAll("\\s+collate\\s+[a-z0-9_-]+", "")
                    .trim();

            String lengthPart = null;
            int parenIndex = sanitized.indexOf('(');
            if (parenIndex >= 0) {
                int endIndex = sanitized.indexOf(')', parenIndex);
                if (endIndex > parenIndex) {
                    lengthPart = sanitized.substring(parenIndex + 1, endIndex).trim();
                }
            }

            String typeToken = parenIndex >= 0 ? sanitized.substring(0, parenIndex) : sanitized;
            typeToken = typeToken.trim();

            String baseType = normalizeBaseType(typeToken);
            String baseWithArguments = (lengthPart != null && !lengthPart.isBlank())
                    ? baseType + "(" + lengthPart + ")"
                    : baseType;

            return new ParsedSqlType(raw, sanitized, baseType, baseWithArguments, lengthPart);
        }

        private static String normalizeBaseType(String typeToken) {
            if (typeToken == null) {
                return "";
            }
            switch (typeToken) {
                case "integer":
                case "int4":
                    return "int";
                case "int2":
                    return "smallint";
                case "int1":
                    return "tinyint";
                case "int8":
                    return "bigint";
                case "serial4":
                    return "serial";
                case "serial8":
                    return "bigserial";
                case "character varying":
                case "varchar2":
                case "nvarchar":
                case "nvarchar2":
                    return "varchar";
                case "character":
                case "national character":
                case "nchar":
                    return "char";
                case "double precision":
                case "float8":
                    return "double";
                case "float4":
                    return "float";
                case "timestamp without time zone":
                case "timestamp with time zone":
                case "timestampz":
                    return "timestamp";
                case "time without time zone":
                case "time with time zone":
                    return "time";
                default:
                    return typeToken;
            }
        }

        String getNormalized() {
            return normalized;
        }

        String getBaseType() {
            return baseType;
        }

        String getBaseWithArguments() {
            return baseWithArguments;
        }

        Integer getPrimaryLength() {
            if (lengthPart == null || lengthPart.isBlank()) {
                return null;
            }
            String[] segments = lengthPart.split(",");
            if (segments.length == 0) {
                return null;
            }
            try {
                return Integer.parseInt(segments[0].trim());
            } catch (NumberFormatException ex) {
                return null;
            }
        }

        Integer getScale() {
            if (lengthPart == null || lengthPart.isBlank()) {
                return null;
            }
            String[] segments = lengthPart.split(",");
            if (segments.length >= 2) {
                try {
                    return Integer.parseInt(segments[1].trim());
                } catch (NumberFormatException ex) {
                    return null;
                }
            }
            if (segments.length == 1) {
                return 0;
            }
            return null;
        }
    }

    private Column extractColumn(SelectItem selectItem) {
        try {
            Method getExpression = selectItem.getClass().getMethod("getExpression");
            Object expression = getExpression.invoke(selectItem);
            if (expression instanceof Column) {
                return (Column) expression;
            }
        } catch (ReflectiveOperationException ex) {
            log.debug("Skip select item due to reflection failure: {}", ex.getMessage());
        }
        return null;
    }

    private String extractAlias(SelectItem selectItem, String defaultName) {
        try {
            Method getAlias = selectItem.getClass().getMethod("getAlias");
            Object aliasObj = getAlias.invoke(selectItem);
            if (aliasObj == null) {
                return defaultName;
            }
            try {
                Method getName = aliasObj.getClass().getMethod("getName");
                Object aliasName = getName.invoke(aliasObj);
                if (aliasName != null) {
                    return aliasName.toString();
                }
            } catch (ReflectiveOperationException ignored) {
                return aliasObj.toString();
            }
            return aliasObj.toString();
        } catch (ReflectiveOperationException ex) {
            log.debug("Fallback to default alias due to reflection failure: {}", ex.getMessage());
            return defaultName;
        }
    }

    private int extractErrorPosition(String errorMessage) {
        if (errorMessage == null) return -1;

        // JSqlParser 错误消息格式通常包含位置信息
        // 例如: "Was expecting one of: K_PRIMARY, K_UNIQUE ... at line 1, column 15"
        if (errorMessage.contains("column ")) {
            String[] parts = errorMessage.split("column ");
            if (parts.length > 1) {
                try {
                    String columnStr = parts[1].split(" ")[0];
                    return Integer.parseInt(columnStr);
                } catch (NumberFormatException e) {
                    log.debug("Failed to parse error position from: {}", errorMessage);
                }
            }
        }
        return -1;
    }

    private String generateParseSuggestion(String sql, int errorPosition) {
        if (errorPosition <= 0) {
            return "请检查 SQL 语法是否正确，确保关键字拼写正确，括号匹配等。";
        }

        String context = errorPosition < sql.length() ?
            sql.substring(Math.max(0, errorPosition - 10), Math.min(sql.length(), errorPosition + 10)) :
            sql;

        StringBuilder suggestion = new StringBuilder();
        suggestion.append("请检查位置 '").append(context).append("' 附近的语法：\n");
        suggestion.append("1. 检查字段名是否使用反引号或双引号包围\n");
        suggestion.append("2. 检查数据类型是否正确（如 VARCHAR(50)、BIGINT 等）\n");
        suggestion.append("3. 检查是否有遗漏的逗号或分号\n");
        suggestion.append("4. 检查括号是否匹配");

        return suggestion.toString();
    }
}